package com.wt.dao;

import com.wt.entity.WqunAccount;
import com.wt.entity.utilEntity.login.LoginAccount;
import com.wt.util.R.R;
import com.wt.util.jdbc.Util;
import org.apache.commons.dbutils.QueryRunner;
import org.apache.commons.dbutils.handlers.BeanHandler;
import org.apache.commons.dbutils.handlers.BeanListHandler;
import org.apache.commons.dbutils.handlers.ScalarHandler;

import java.sql.Connection;
import java.sql.SQLException;
import java.text.SimpleDateFormat;
import java.util.Date;
import java.util.List;

public class WqunAccountDao {
    //建立数据库连接，以便进行事务处理
    Connection con;
    {
        try {
            con = Util.getCon().getConnection();
        } catch (SQLException e) {
            throw new RuntimeException(e);
        }
    }


    /**
     * 查询数据库，方法由 service 层 调用
     */
    //实现化数据访问层中QueryRunner对象
    private QueryRunner qr = new QueryRunner(Util.getCon());



    public boolean deleteById(int id){
        try {
            String sql = "update wqun_account set is_delete=1 where id=?";
            return qr.update(sql, id) >= 1;
        } catch (SQLException throwables) {
            throwables.printStackTrace();
        }
        return false;
    }





    public int findAccountCount() {
        try {
            String sql = "SELECT count(*) FROM wqun_account where  `is_delete` = 0";
            Long count = (Long) qr.query(sql, new ScalarHandler());
            return Math.toIntExact(count);
        } catch (SQLException throwables) {
            return -1;
        }

    }


    public List<WqunAccount> findAccountPage(int pageSize, int currentPage, String selectValue, String inputValue) {
        if(selectValue != null && inputValue != null){
            int firstTotal = currentPage *  pageSize;
            inputValue = "%" + inputValue + "%";
            try {
                String sql = null;
                switch (selectValue){
                    case "phone":
                        sql = "SELECT id ,phone,user_name userName,nick_name nickName,sex,user_type userType,email,avatar,`pass`,salt,is_delete isDelete,create_by createBy,created_time createdTime,modified_time modifiedTime,update_by updateBy FROM wqun_account where phone like ? and `is_delete` = 0 limit ?,?";
                        break;
                    case "user_name":
                        sql = "SELECT id ,phone,user_name userName,nick_name nickName,sex,user_type userType,email,avatar,`pass`,salt,is_delete isDelete,create_by createBy,created_time createdTime,modified_time modifiedTime,update_by updateBy FROM wqun_account where user_name and `is_delete` = 0 like ? limit ?,?";
                        break;
                    case "nick_name":
                        sql = "SELECT id ,phone,user_name userName,nick_name nickName,sex,user_type userType,email,avatar,`pass`,salt,is_delete isDelete,create_by createBy,created_time createdTime,modified_time modifiedTime,update_by updateBy FROM wqun_account where nick_name and `is_delete` = 0 like ? limit ?,?";
                        break;
                }
                return qr.query(sql, new BeanListHandler<WqunAccount>(WqunAccount.class),inputValue,firstTotal,pageSize);
            } catch (SQLException throwables) {
                return null;
            }
        }else{
            int firstTotal = currentPage *  pageSize;
            try {
                String sql = "SELECT id ,phone,user_name userName,nick_name nickName,sex,user_type userType,email,avatar,`pass`,salt,is_delete isDelete,create_by createBy,created_time createdTime,modified_time modifiedTime,update_by updateBy FROM wqun_account where `is_delete` = 0 limit ?,?";
                return qr.query(sql, new BeanListHandler<WqunAccount>(WqunAccount.class),firstTotal,pageSize);
            } catch (SQLException throwables) {
                return null;
            }
        }

    }



    public List<WqunAccount> listAll() {
        try {
            String sql = "SELECT id ,phone,user_name userName,nick_name nickName,sex,user_type userType,email,avatar,`pass`,salt,is_delete isDelete,create_by createBy,created_time createdTime,modified_time modifiedTime,update_by updateBy FROM wqun_account where `is_delete` = 0";
            return qr.query(sql, new BeanListHandler<WqunAccount>(WqunAccount.class));
        } catch (SQLException throwables) {
            return null;
        }

    }





    /**
     * 根据 手机 查询
     * @return 返回要查询的员工对象的 list 集合
     * 创建用户，不管该用户是否删除，该手机号已经存在
     */
    public List<WqunAccount> addAccountByPhone(String phone){
        try {
            String sql = "SELECT id ,phone,user_name userName,nick_name nickName,sex,user_type userType,email,avatar,`pass`,salt,is_delete isDelete,create_by createBy,created_time createdTime,modified_time modifiedTime,update_by updateBy FROM wqun_account where phone = ?";
            return qr.query(sql, new BeanListHandler<WqunAccount>(WqunAccount.class),phone);
        } catch (SQLException throwables) {
            return null;
        }
    }

    public List<WqunAccount> getOneById(int id){
        try {
            String sql = "SELECT id ,phone,user_name userName,nick_name nickName,sex,user_type userType,email,avatar,`pass`,salt,is_delete isDelete,create_by createBy,created_time createdTime,modified_time modifiedTime,update_by updateBy FROM wqun_account where id = ?";
            return qr.query(sql, new BeanListHandler<WqunAccount>(WqunAccount.class),id);
        } catch (SQLException throwables) {
            return null;
        }
    }

    /**
     * 根据 手机 查询
     * @return 返回要查询的员工对象的 list 集合
     */
    public List<WqunAccount> selectWqunAccountPhone(String phone){
        try {
            String sql = "SELECT id ,phone,user_name userName,nick_name nickName,sex,user_type userType,email,avatar,`pass`,salt,is_delete isDelete,create_by createBy,created_time createdTime,modified_time modifiedTime,update_by updateBy FROM wqun_account where phone = ? and is_delete=0";
            return qr.query(sql, new BeanListHandler<WqunAccount>(WqunAccount.class),phone);
        } catch (SQLException throwables) {
            return null;
        }
    }

    /**
     * 根据 昵称 查询
     * @return 返回要查询的员工对象的 list 集合
     */
    public List<WqunAccount> selectByNickName(String nickName){
        try {
            String sql = "SELECT id ,phone,user_name userName,nick_name nickName,sex,user_type userType,email,avatar,`pass`,salt,is_delete isDelete,create_by createBy,created_time createdTime,modified_time modifiedTime,update_by updateBy FROM wqun_account where nick_name = ? and is_delete=1";
            return qr.query(sql, new BeanListHandler<WqunAccount>(WqunAccount.class),nickName);
        } catch (SQLException throwables) {
            return null;
        }
    }

    public boolean save(WqunAccount wqunAccount){
        try {
//            String sql = "select count(*)+202301 from wqun_account";
//            Object query = qr.query(sql, new ScalarHandler());
            String sql1 =
                    "insert into wqun_account values(null,?,null,?,2,1,null,null,?,null,0,null,?,null,null) ";
            //调用 qr 对象中的方法 update 执行添加,自动添加
            Date date = new Date();
            SimpleDateFormat simpleDateFormat = new SimpleDateFormat("yyyy-MM-dd hh:mm:ss");
            String createdTime = simpleDateFormat.format(date);
            return qr.update(sql1, wqunAccount.getPhone(),wqunAccount.getNickName(),wqunAccount.getPass(),createdTime)>0;
            //System.out.println(iret > 0 ? "添加成功":"添加失败");
        } catch (SQLException throwables) {
            throwables.printStackTrace();
        }
        return false;
    }


    public boolean updatePass(WqunAccount wqunAccount){
        try {
            String sql =
                    "UPDATE wqun_account  SET `pass` = ? where phone = ?";

            return  qr.update(sql,wqunAccount.getPass(),wqunAccount.getPhone()) > 0;

        } catch (SQLException throwables) {
            throwables.printStackTrace();
        }
        return false;
    }

    public boolean update(WqunAccount wqunAccount){
        try {
            String sql =
                    "UPDATE wqun_account  SET `user_name` = ?,`nick_name` = ?,`sex` = ?,`email` = ?,`avatar` = ?,`salt` = ? where phone = ?";
            return  qr.update(sql,wqunAccount.getUserName(),wqunAccount.getNickName(),wqunAccount.getSex(),wqunAccount.getEmail(),wqunAccount.getAvatar(),wqunAccount.getSalt(),wqunAccount.getPhone()) > 0;

        } catch (SQLException throwables) {
            throwables.printStackTrace();
        }
        return false;
    }




}
